/* $Id: SingleClientPersonRoleDAOImpl.java 
 * Copyright (C) 2011, TRAKHEES
 *  
 * Revision History
 *
 * Date  			Author 		    	Remarks
 * ---------------------------------------------------------------------------------
 *
 */
package ae.pcfc.etks.online.common.dao.provider;

import java.sql.Types;
import java.util.Calendar;
import java.util.List;
import java.util.Map;

import org.springframework.jdbc.core.SqlOutParameter;
import org.springframework.jdbc.core.SqlParameter;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.core.simple.ParameterizedBeanPropertyRowMapper;
import org.springframework.jdbc.core.simple.SimpleJdbcCall;
import org.springframework.stereotype.Repository;

import ae.pcfc.etks.online.common.ConstantsCommon;
import ae.pcfc.etks.online.common.dao.SingleClientPersonRoleDAO;
import ae.pcfc.etks.online.common.vo.SCClientPersonRoleVO;

@Repository
public class SingleClientPersonRoleDAOImpl extends SingleClientBaseDAO implements
		SingleClientPersonRoleDAO {

	/**
	 * add
	 * 
	 * @see ae.pcfc.etks.online.common.dao.SingleClientPersonRoleDAO#addOrUpdate(java.lang.Long, java.lang.String, java.lang.String)
	 * 
	 */
	public String addOrUpdate(Long roleReqId, String trakheesId, String strUserName, String strStatus) {
		SimpleJdbcCall jdbcCall = new SimpleJdbcCall(getJdbcTemplate())
				.withProcedureName(ConstantsCommon.SINGLE_CLIENT_PERSON_ROLE_INSERT);
		jdbcCall.setAccessCallParameterMetaData(false);// As there was some
														// issue with the driver

		jdbcCall = jdbcCall.declareParameters(
				new SqlParameter("P_TSC_PARTNER_SYSTEM_ID", Types.VARCHAR), 
				new SqlParameter("P_CP_TRAKHEES_ID", Types.VARCHAR), 
				new SqlParameter("P_CR_ID", Types.NUMERIC),
				new SqlParameter("P_CPR_STATUS", Types.VARCHAR),
				new SqlParameter("P_CPR_CREATEDUSER", Types.VARCHAR),
				new SqlParameter("P_CPR_CREATEDTIME", Types.DATE), 
				new SqlParameter("P_CPR_MODIFIEDUSER", Types.VARCHAR), 
				new SqlParameter("P_CPR_MODIFIEDTIME", Types.DATE),
				new SqlOutParameter("P_INSERTED", Types.VARCHAR));

		SqlParameterSource in = new MapSqlParameterSource()
				.addValue("P_TSC_PARTNER_SYSTEM_ID",
						ConstantsCommon.PARTNER_SYSTEM_ID)
				.addValue("P_CP_TRAKHEES_ID", trakheesId)
				.addValue("P_CR_ID", roleReqId)
				.addValue("P_CPR_STATUS", strStatus)
				.addValue("P_CPR_CREATEDUSER", strUserName)
				.addValue("P_CPR_CREATEDTIME", Calendar.getInstance().getTime())
				.addValue("P_CPR_MODIFIEDUSER", strUserName)
				.addValue("P_CPR_MODIFIEDTIME", Calendar.getInstance().getTime());

		@SuppressWarnings("rawtypes")
		Map result = jdbcCall.execute(in);
		return result.get("P_INSERTED").toString();
		//return null;
	}

	/**
	 * findSCPersonRoleByTrakheesId
	 * 
	 * @see ae.pcfc.etks.online.common.dao.SingleClientPersonRoleDAO#findSCPersonRoleByTrakheesId(java.lang.String)
	 * 
	 */
	public List<SCClientPersonRoleVO> findSCPersonRoleByTrakheesId(String trakheesId) {
		@SuppressWarnings("deprecation")
		SimpleJdbcCall jdbcCall = new SimpleJdbcCall(jdbcTemplate)
				.withProcedureName("P_GET_CLNT_PRSN_ROLE_BY_TKSID")
				.returningResultSet(
						"p_RECORDSET",
						ParameterizedBeanPropertyRowMapper
								.newInstance(SCClientPersonRoleVO.class));
		
		jdbcCall.setAccessCallParameterMetaData(false);// As there was some
														// issue with the driver
		jdbcCall = jdbcCall.declareParameters(new SqlParameter("P_TRAKHEES_ID",
				Types.VARCHAR));
		SqlParameterSource in = new MapSqlParameterSource().addValue(
				"P_TRAKHEES_ID", trakheesId);
		@SuppressWarnings("rawtypes")
		Map result = jdbcCall.execute(in);
		
		@SuppressWarnings("unchecked")
		List<SCClientPersonRoleVO> resultObj = (List<SCClientPersonRoleVO>) result.get("p_RECORDSET");
		
		if (resultObj != null && resultObj.size() > 0) {
			return resultObj;
		} else
			return null;
	}

}
